﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RM2012Service.DataService;
using System.Data.SqlClient;
using System.Data;
using RM2012Service.Business;

namespace RM2012Service.DataAccess
{
    public class UsersDAO
    {
        DBConnection db;

        public UsersDAO()
        {
            db = new DBConnection();
        }

        public DataTable GetAllUsers()
        {
            return db.ExecuteDataTable("GetAllUsers", null);
        }

        public DataTable GetUserById(string userId)
        {
            SqlParameter param = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param.Value = userId;
            return db.ExecuteDataTable("GetUserById", param);
        }


        public DataTable GetUserByUserName(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param.Value = userName;
            return db.ExecuteDataTable("GetUserByUserName", param);
        }

        public DataTable GetUserByFullName(string fullName)
        {
            SqlParameter param = new SqlParameter("@FullName", SqlDbType.NVarChar, 100);
            param.Value = fullName;
            return db.ExecuteDataTable("GetUserByFullName", param);
        }

        public DataTable GetUserByAddress(string address)
        {
            SqlParameter param = new SqlParameter("@Address", SqlDbType.NVarChar, 200);
            param.Value = address;
            return db.ExecuteDataTable("GetUserByAddress", param);
        }

        public DataTable GetUserByPhone(string phone)
        {
            SqlParameter param = new SqlParameter("@Phone", SqlDbType.NVarChar, 100);
            param.Value = phone;
            return db.ExecuteDataTable("GetUserByPhone", param);
        }

        public DataTable GetUserByEmail(string email)
        {
            SqlParameter param = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param.Value = email;
            return db.ExecuteDataTable("GetUserByEmail", param);
        }

        public DataTable GetUserByGroup(string groupId)
        {
            SqlParameter param = new SqlParameter("@UserGroupId", SqlDbType.VarChar, 4);
            param.Value = groupId;
            return db.ExecuteDataTable("GetUserByGroup", param);
        }

        public object UserHasGroup(string userId)
        {
            SqlParameter param = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param.Value = userId;
            return db.ExecuteScalar("UserHasGroup", param);
        }

        public object ExistUserByUserName(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param.Value = userName;
            return db.ExecuteScalar("ExistUserByUserName", param);
        }

        public object ExistUserByUserNameAndPassword(string userName, string passWord)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[0].Value = userName;

            param[1] = new SqlParameter("@PassWord", SqlDbType.VarChar, 50);
            param[1].Value = passWord;

            return db.ExecuteScalar("ExistUserByUserNameAndPassword", param);
        }

        public DataTable GetUserGroup(string userId)
        {
            SqlParameter param = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param.Value = userId;
            return db.ExecuteDataTable("GetUserGroup", param);
        }

        public int AddUser(UsersInfo info)
        {
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param[0].Value = info.UserId;

            param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[1].Value = info.UserName;

            param[2] = new SqlParameter("@PassWord", SqlDbType.VarChar, 50);
            param[2].Value = info.PassWord;

            param[3] = new SqlParameter("@FullName", SqlDbType.NVarChar, 100);
            param[3].Value = info.FullName;

            param[4] = new SqlParameter("@Salary", SqlDbType.Real);
            param[4].Value = info.Salary;

            param[5] = new SqlParameter("@Address", SqlDbType.NVarChar, 200);
            param[5].Value = info.Address;

            param[6] = new SqlParameter("@Phone", SqlDbType.NVarChar, 100);
            param[6].Value = info.Phone;

            param[7] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[7].Value = info.Email;

            param[8] = new SqlParameter("@Sex", SqlDbType.Bit);
            param[8].Value = info.Sex;

            param[9] = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);
            param[9].Value = info.Remark;

            return db.ExecuteNoneQuery("AddUser", param);
        }

        public int UpdateUser(UsersInfo info)
        {
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param[0].Value = info.UserId;

            param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[1].Value = info.UserName;

            param[2] = new SqlParameter("@PassWord", SqlDbType.VarChar, 50);
            param[2].Value = info.PassWord;

            param[3] = new SqlParameter("@FullName", SqlDbType.NVarChar, 100);
            param[3].Value = info.FullName;

            param[4] = new SqlParameter("@Salary", SqlDbType.Real);
            param[4].Value = info.Salary;

            param[5] = new SqlParameter("@Address", SqlDbType.NVarChar, 200);
            param[5].Value = info.Address;

            param[6] = new SqlParameter("@Phone", SqlDbType.NVarChar, 100);
            param[6].Value = info.Phone;

            param[7] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[7].Value = info.Email;

            param[8] = new SqlParameter("@Sex", SqlDbType.Bit);
            param[8].Value = info.Sex;

            param[9] = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);
            param[9].Value = info.Remark;

            return db.ExecuteNoneQuery("UpdateUser", param);
        }

        public int UpdateUserNoPass(UsersInfo info)
        {
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param[0].Value = info.UserId;

            param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[1].Value = info.UserName;

            param[2] = new SqlParameter("@FullName", SqlDbType.NVarChar, 100);
            param[2].Value = info.FullName;

            param[3] = new SqlParameter("@Salary", SqlDbType.Real);
            param[3].Value = info.Salary;

            param[4] = new SqlParameter("@Address", SqlDbType.NVarChar, 200);
            param[4].Value = info.Address;

            param[5] = new SqlParameter("@Phone", SqlDbType.NVarChar, 100);
            param[5].Value = info.Phone;

            param[6] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[6].Value = info.Email;

            param[7] = new SqlParameter("@Sex", SqlDbType.Bit);
            param[7].Value = info.Sex;

            param[8] = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);
            param[8].Value = info.Remark;

            return db.ExecuteNoneQuery("UpdateUserNoPass", param);
        }

        public int UpdateProfile(UsersInfo info)
        {
            SqlParameter[] param = new SqlParameter[5];
            param[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param[0].Value = info.UserId;

            param[1] = new SqlParameter("@FullName", SqlDbType.NVarChar, 100);
            param[1].Value = info.FullName;

            param[2] = new SqlParameter("@Address", SqlDbType.NVarChar, 200);
            param[2].Value = info.Address;

            param[3] = new SqlParameter("@Phone", SqlDbType.NVarChar, 100);
            param[3].Value = info.Phone;

            param[4] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[4].Value = info.Email;

            return db.ExecuteNoneQuery("UpdateProfile", param);
        }

        public int ChangePassword(string userId, string passWord)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param[0].Value = userId;

            param[1] = new SqlParameter("@PassWord", SqlDbType.VarChar, 50);
            param[1].Value = passWord;

            return db.ExecuteNoneQuery("ChangePassword", param);
        }

        public object GetIdByUsername(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param.Value = userName;

            return db.ExecuteScalar("GetUserIdByUsername", param);
        }

        public int DeleteUser(string userId)
        {
            SqlParameter param = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
            param.Value = userId;

            return db.ExecuteNoneQuery("DeleteUser", param);
        }
    }
}
